from java.lang import System, Class, ClassNotFoundException
from java.sql import DriverManager, SQLException
from java.awt import Desktop
from java.io import IOException
from java.net import URISyntaxException 
from java.net import URI
from javax.swing import JButton
from javax.swing import JFrame
from javax.swing import JPanel
from javax.swing import JTextField
from javax.swing import JMenu
from javax.swing import JMenuBar
from javax.swing import JMenuItem
from javax.swing import JComboBox
from java.awt import Color
from javax.swing import JList
from java.awt.event import KeyListener, ActionListener
import sys

  

class Data:
    def __init__(self):
        self.createInitialDatabase()
        
    def createInitialDatabase(self):
        print 'Database ready to be created'
        try:
            System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
            Class.forName("org.sqlite.JDBC")
            connection = DriverManager.getConnection("jdbc:sqlite:favorites")
            statement = connection.createStatement()
    
    
        except ClassNotFoundException, e:
            sys.exit(e.message)
        except SQLException, e:
            sys.exit(e.message)  

        createSongs = "CREATE TABLE if not exists songs(song_name TEXT, link TEXT, artist_name TEXT, album_name TEXT) "      
        createArtists = "CREATE TABLE if not exists artists(artist_name TEXT, artist_id INT)"
        createAlbums = "CREATE TABLE if not exists albums(album_name TEXT, artist_name TEXT, genre TEXT, year INT)"

        statement.addBatch(createSongs)
        statement.addBatch(createArtists)
        statement.addBatch(createAlbums)
        statement.executeBatch()
        
        
        for t in [('hey mama','http://www.youtube.com/watch?v=RtpKqMpqGU0','kanye west','college dropout' ),
                  ('the new workout plan','http://www.youtube.com/watch?v=Ua61XY84gGg&ob=av2e','kanye West','college dropout'),
                  ('through the wire','http://www.youtube.com/watch?v=uvb-1wjAtk4','kanye West','college dropout'),
                  ('jesus walks','http://www.youtube.com/watch?v=b8AyHupByuU','kanye west','college dropout'),
                  ('all falls down','http://www.youtube.com/watch?v=8kyWDhB_QeI&ob=av2e','kanye west','college dropout'),
                  ('spaceship','http://www.youtube.com/watch?v=wGM6N0qXeu4','kanye west','college dropout'),
                  ('never let me down','http://www.youtube.com/watch?v=9sHpHQTuZC4','kanye West','college dropout'),
                  ('family business','http://www.youtube.com/watch?v=z8yoxyIu0s8','kanye west','college dropout'),
                  ('last call','http://www.youtube.com/watch?v=V9mwuYBljUA','kanye west','college dropout'),
                  ('the scientist','http://www.youtube.com/watch?v=EqWLpTKBFcU&ob=av2e','coldplay','a rush of blood to the head'),
                  ('in my place','http://www.youtube.com/watch?v=yEoHFzEmld0&ob=av2e', 'coldplay','a rush of blood to the head'),
                  ('god put a smile on my face','http://www.youtube.com/watch?v=e9Kcg_8gK30&ob=av2e','coldplay','a rush of blood to the head'),
                  ('clocks','http://www.youtube.com/watch?v=d020hcWA_Wg&ob=av2n','coldplay','a rush to the blood to the head'),
                  ('yellow','http://www.youtube.com/watch?v=1MwjX4dG72s&ob=av2e','coldplay','parachutes',),
                  ('fix you','http://www.youtube.com/watch?v=JI-o25K6B-E&ob=av2e','coldplay','x&y'),
                  ('speed of sound','http://www.youtube.com/watch?v=TahH7B_aUZc&ob=av2n','coldplay','x&y'),
                  ('the hardest part','http://www.youtube.com/watch?v=1Tp0r9197uo','coldplay','x&y'),
                  ('talk','http://www.youtube.com/watch?v=W0uqLM1uj_k&ob=av2e','coldplay','x&y'),
                  ('lost','http://www.youtube.com/watch?v=7HkjGg_pPJU&ob=av2n','coldplay','viva la vida'),
                  ('thriller','http://www.youtube.com/watch?v=sOnqjkJTMaA&ob=av2n','michael jackson','thriller'),
                  ('billie jean','http://www.youtube.com/watch?v=Zi_XLOBDo_Y&ob=av2n','michael jackson','thriller'),
                  ('beat it','http://www.youtube.com/watch?v=oRdxUFDoQe0&ob=av2e','michael jackson','thriller'),
                  ('one love', 'http://www.youtube.com/watch?v=vdB-8eLEW8g','bob marley', 'the very best of bob marley'),
                  ('three little birds','http://www.youtube.com/watch?v=kIjkW6iyXNo', 'bob marley', 'the very best of bob marley'),
                  ('no woman no cry','http://www.youtube.com/watch?v=n4kpqDF9j6Q','bob marley','the very best of bob marley'),
                  ('homecoming', 'http://www.youtube.com/watch?v=LQ488QrqGE4&feature=relmfu', 'kanye west','graduation'), ]:
            statement.execute("INSERT INTO songs VALUES" + "('" + t[0]+ "','" + t[1] + "','" + t[2] + "','" + t[3] + "')")
            
        #rs = statement.executeQuery('SELECT * FROM songs')
        #columns = 3
        #while rs.next():
            #for c in range(1, columns + 1):
             #   print rs.getString(c), '\t',
            #print
        
    def showAllSongs(self):
        System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
        Class.forName("org.sqlite.JDBC")
        connection = DriverManager.getConnection("jdbc:sqlite:favorites")
        statement = connection.createStatement()
        rs = statement.executeQuery('SELECT * FROM songs')
        columns = 3
        while rs.next():
            for c in range(1, columns + 1):
                print rs.getString(c), '\t',
            print
            
    def addSong(self,song, link, artist, album):
        System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
        Class.forName("org.sqlite.JDBC")
        connection = DriverManager.getConnection("jdbc:sqlite:favorites")
        statement = connection.createStatement()
        statement.execute("INSERT INTO songs VALUES" + "('" + song+ "','" + link + "','" + artist + "','" + album + "')")
        rs = statement.executeQuery("SELECT * FROM songs") 
    
        columns = 4
        while rs.next():
            for c in range(1, columns + 1):
                print rs.getString(c), '\t',
            print

    def searchSong (self,song):
        System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
        Class.forName("org.sqlite.JDBC")
        connection = DriverManager.getConnection("jdbc:sqlite:favorites")
        statement = connection.createStatement()
        rs = statement.executeQuery("SELECT song_name, song_link, artist_name FROM songs WHERE song_name LIKE %" +song+ "%")
    
    def openSong (self, song): 
        System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
        Class.forName("org.sqlite.JDBC")
        connection = DriverManager.getConnection("jdbc:sqlite:favorites")
        statement = connection.createStatement()
    
        openS = statement.executeQuery("SELECT link FROM songs WHERE artist_name = '"+ song.lower() + "' OR song_name = '"+ song.lower() + "'")
        columns = 1
        for c in range(1,columns+1):
            link = openS.getString(c)
        
        try:  
            if Desktop.isDesktopSupported():
                desktop = Desktop.getDesktop()
                uri = URI(link)
                desktop.browse(uri)
        except IOException, e:
            e.printStackTrace(e)
        
        except URISyntaxException, e:
            e.printStackTrace(e)  
    
    def checkIfExists(self, song):
        System.setProperty("sqlite.purejava", "true") # Strictly optional; use if you get UnsatisfiedLinkError or the like.
        Class.forName("org.sqlite.JDBC")
        connection1 = DriverManager.getConnection("jdbc:sqlite:favorites")
        statement = connection1.createStatement()
    
        openS = statement.executeQuery("SELECT song_name FROM songs WHERE song_name = '"+ song.lower() + "'" )
        columns = 1   
                 
        for c in range(1,columns+1):         
            if song.lower() == openS.getString(c):
                print ' it exists '    
                return True
                 
class GUI(JFrame):
    def __init__(self):
        super(GUI, self).__init__()
        self.initUI()        

    def initUI(self):
        self.panel = JPanel()        
        self.artists = [ "Bob Marley", "Coldplay", "Kanye West", "Michael Jackson" ]       
        self.menu = Menu()               
        self.searchbar = SearchBar()       
        self.getter = ArtistGetter(self)
        self.artistmenu = JComboBox(self.artists)
        self.artistmenu.addActionListener(self.getter)
        self.artistmenu.setBounds(100, 120, 350, 30)
        self.panel.add(self.artistmenu)              
        self.getContentPane().add(self.panel)
        self.setJMenuBar(self.menu.makeMe())       
        self.panel.add(self.searchbar.makeMe())        
        button = JButton("Search", actionPerformed= self.searchbar.getSearch)
        button.setBounds(370, 90, 100, 30)
        self.panel.add(button)
        self.panel.setLayout(None)       
        self.setTitle("Favorite Song Database")
        self.setSize(615, 400)
        self.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE)
        self.setLocationRelativeTo(None)
        self.setVisible(True)
        self.setResizable(False)
        self.panel.setBackground(Color.GREEN)
 
    
class ArtistGetter(ActionListener):
    def __init__(self, frame):
        self.outer = frame
    
    def actionPerformed(self, e):
        song = self.outer.artistmenu.getSelectedItem()               
        gui.panel.add(makeOutputList([song]))
        gui.panel.setBackground(Color.YELLOW)
        database.openSong(song)

class Menu(JMenuBar):
    def __init__(self):        
        self.menu = JMenuBar()
        self.file = JMenu('File')
        self.add = JMenuItem('Add')
        self.remove = JMenuItem('Remove')
        
    def makeMe(self):       
        self.menu.add(self.file)
        self.file.add(self.add)
        self.file.add(self.remove)
        return self.menu            

    
class SearchBar(JTextField, KeyListener):    
    def __init__(self):         
        self.searchbar = JTextField()              
       
    def makeMe(self):                       
        self.searchbar.setBounds(100, 90, 270, 30)         
        return self.searchbar      

    def getSearch(self, e):
        song = self.searchbar.getText()     
        database.openSong(song)
                      
       

        
class makeOutputList(JList): 
    def __init__(self, content):
        self.content = content
        self.outputlist = JList(self.content)      
        
    def makeMe(self):        
        self.outputlist.setBounds(100, 190, 370, 90)
        return self.outputlist
    
    def getSelectedOutput(self):
        print(self.outputlist.getSelectedValue())
        return(self.ouputlist.getSelectedValue())        
        
        
if __name__ == '__main__':
    gui = GUI()
    database = Data()            
    #Database.checkIfExists("Thriller")
    

    